﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FlorDeLiz.BusinessEntitites;
using MySql.Data.MySqlClient;
using System.Data;
using Constants = FlorDeLiz.Utils.Constants;

namespace FlorDeLiz.DataAccess
{

    public class VendedorDA : BaseDA
    {
        #region Singleton
        private static VendedorDA instancia;
        public static VendedorDA Instancia
        {
            get
            {
                if (instancia == null)
                {
                    instancia = new VendedorDA();
                }
                return instancia;
            }
        }
        #endregion

        public List<Vendedor> ObterVendedores(int? id, string nome, string codigo, string cpf)
        {
            List<Vendedor> vendedores = new List<Vendedor>();
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }
                CommandSql = new MySqlCommand(Constants.Procedures.SP_SELECT_VENDEDOR, Conexao);

                if (id.HasValue)
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_ID_VENDEDOR", MySqlDbType.Int32, 11, ParameterDirection.Input, id.Value));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_ID_VENDEDOR", MySqlDbType.Int32, 11, ParameterDirection.Input, DBNull.Value));
                }

                if (!string.IsNullOrEmpty(nome))
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_NM_VENDEDOR", MySqlDbType.String, 80, ParameterDirection.Input, nome));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_NM_VENDEDOR", MySqlDbType.String, 80, ParameterDirection.Input, DBNull.Value));
                }

                if (!string.IsNullOrEmpty(codigo))
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_COD_VENDEDOR", MySqlDbType.String, 10, ParameterDirection.Input, codigo));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_COD_VENDEDOR", MySqlDbType.String, 10, ParameterDirection.Input, DBNull.Value));
                }

                if (!string.IsNullOrEmpty(cpf))
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_CPF_VENDEDOR", MySqlDbType.String, 11, ParameterDirection.Input, cpf));
                }
                else
                {
                    CommandSql.Parameters.Add(AdicionarParametro("P_CPF_VENDEDOR", MySqlDbType.String, 11, ParameterDirection.Input, DBNull.Value));
                }

                CommandSql.CommandType = System.Data.CommandType.StoredProcedure;
                using (MySqlDataReader reader = CommandSql.ExecuteReader())
                {
                    vendedores = PreencherVendedores(reader);
                }
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }
            return vendedores;
        }

        private List<Vendedor> PreencherVendedores(MySqlDataReader reader)
        {
            List<Vendedor> vendedores = new List<Vendedor>();
            Vendedor vendedor;
            while (reader.Read())
            {
                vendedor = new Vendedor();
                vendedor.Id = (int)reader["ID_VENDEDOR"];
                vendedor.Codigo = reader["COD_VENDEDOR"].ToString();
                vendedor.Nome = reader["NM_VENDEDOR"].ToString();
                vendedor.CPF = reader["CPF_VENDEDOR"].ToString();

                vendedores.Add(vendedor);
            }

            return vendedores;
        }

        public Vendedor InserirVendedor(Vendedor vendedor)
        {
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }

                CommandSql = new MySqlCommand(Constants.Procedures.SP_INSERT_VENDEDOR, Conexao);

                CommandSql.Parameters.Add(AdicionarParametro("P_COD_VENDEDOR", MySqlDbType.String, 10, ParameterDirection.Input, vendedor.Codigo));
                CommandSql.Parameters.Add(AdicionarParametro("P_NM_VENDEDOR", MySqlDbType.String, 80, ParameterDirection.Input, vendedor.Nome));
                CommandSql.Parameters.Add(AdicionarParametro("P_CPF_VENDEDOR", MySqlDbType.String, 11, ParameterDirection.Input, vendedor.CPF));

                CommandSql.CommandType = CommandType.StoredProcedure;
                using (MySqlDataReader reader = CommandSql.ExecuteReader())
                {
                    List<Vendedor> vendedores = PreencherVendedores(reader);
                    if (vendedores != null && vendedores.Count > 0)
                    {
                        vendedor = vendedores[0];
                    }
                }
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }

            return vendedor;
        }

        public Vendedor AlterarVendedor(Vendedor vendedor)
        {
            try
            {
                if (Conexao.State != ConnectionState.Open)
                {
                    Conexao.Open();
                }

                CommandSql = new MySqlCommand(Constants.Procedures.SP_UPDATE_VENDEDOR, Conexao);

                CommandSql.Parameters.Add(AdicionarParametro("P_ID_VENDEDOR", MySqlDbType.Int32, 4, ParameterDirection.Input, vendedor.Id));
                CommandSql.Parameters.Add(AdicionarParametro("P_COD_VENDEDOR", MySqlDbType.String, 10, ParameterDirection.Input, vendedor.Codigo));
                CommandSql.Parameters.Add(AdicionarParametro("P_NM_VENDEDOR", MySqlDbType.String, 80, ParameterDirection.Input, vendedor.Nome));
                CommandSql.Parameters.Add(AdicionarParametro("P_CPF_VENDEDOR", MySqlDbType.String, 11, ParameterDirection.Input, vendedor.CPF));

                CommandSql.CommandType = CommandType.StoredProcedure;

                CommandSql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
                throw ex;
            }
            finally
            {
                if (Conexao.State == ConnectionState.Open)
                {
                    Conexao.Close();
                }
            }

            return vendedor;
        }
    }
}
